package org.tarena.dang.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.tarena.dang.dao.CateGoryDAO;
import org.tarena.dang.entity.Book;
import org.tarena.dang.entity.Categrory;
import org.tarena.dang.util.DbUtil;
   
public class CateGoryDAOImpl implements CateGoryDAO {
	
	private static final String findAll = "select * from d_category";
	private static final String findByParentId = "select dc.*,count(dcp.product_id) as pnum " +
			"from d_category dc left outer join d_category_product dcp  on(dc.id=dcp.cat_id) where " +
			"dc.parent_id=? group by dc.id";
	private static final String findByParetIdClass = "select name from d_category where id = ?";
	@Override
	public List<Categrory> findAll() throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findAll);
		ResultSet re = pst.executeQuery();
		List<Categrory> list = new ArrayList<Categrory>();
		while(re.next()){
			Categrory categrory = new Categrory();
			categrory.setId(re.getInt("id"));
			categrory.setDescription(re.getString("description"));
			categrory.setEnName(re.getString("en_name"));
			categrory.setParentId(re.getInt("parent_id"));
			categrory.setName(re.getString("name"));
			categrory.setTurn(re.getInt("turn"));
			list.add(categrory);
		}
		DbUtil.closeConnection();
		return list;
	}
	@Override
	public List<Categrory> findByParetId(int parentId) throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findByParentId);
		pst.setInt(1, parentId);
		ResultSet re = pst.executeQuery();
		List<Categrory> list = new ArrayList<Categrory>();
		while(re.next()){
			Categrory categrory = new Categrory();
			categrory.setId(re.getInt("id"));
			categrory.setDescription(re.getString("description"));
			categrory.setEnName(re.getString("en_name"));
			categrory.setParentId(re.getInt("parent_id"));
			categrory.setName(re.getString("name"));
			categrory.setTurn(re.getInt("turn"));
			categrory.setPnum(re.getInt("pnum"));
			list.add(categrory);
		}
		DbUtil.closeConnection();
		return list;
	}
	@Override
	public String findByParetIdClass(int parentId) throws SQLException {
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(findByParetIdClass);
		pst.setInt(1, parentId);
		ResultSet re = pst.executeQuery();
		re.next();
		return re.getString(1);
	}
	@Override
	public List<Categrory> searchByKeyWords(String keyWords)
			throws SQLException {
		String sql = "select name,cat_id,parent_id,product_id,count(*) as pnum from v_product where product_name like '%"+keyWords+"%' or author like '%"+keyWords+"%' group by cat_id";
		System.err.println(sql);
		PreparedStatement pst = DbUtil.openConnection().prepareStatement(sql);
		ResultSet re = pst.executeQuery();
		List<Categrory> list = new ArrayList<Categrory>();
		while(re.next()){
			Categrory categrory = new Categrory();
			//categrory.setEnName(re.getString("en_name"));
			categrory.setParentId(re.getInt("parent_id"));
			categrory.setId(re.getInt("cat_id"));
			categrory.setName(re.getString("name"));
			//categrory.setTurn(re.getInt("turn"));
			categrory.setPnum(re.getInt("pnum"));
			list.add(categrory);
		}
		DbUtil.closeConnection();
		System.out.println("该关键字的产品共分为--"+list.size()+"--类");
		return list;
	}

	
}
